package com.css.fxfzxczh.modules.fastDisasterAnalysis.houseDestroy.repository;

import com.css.fxfzxczh.base.response.RestResponse;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.entity.PopulationEvaluateResult;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.entity.ypg.DataPbeIntStrArea;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.houseDestroy.entity.HouseDestroyEvaluateModel;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.houseDestroy.entity.HouseDestroyEvaluateResult;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.houseDestroy.vo.DataPbeInjuriesVo;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import com.css.fxfzxczh.util.PlatformSessionUtils;
import com.css.fxfzxczh.util.UUIDGenerator;
import org.checkerframework.checker.units.qual.A;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年07月28日  15:35
 */
@Repository
public class HouseDestroyRepository {

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    JdbcTemplate jdbcTemplate;
    public List<DataPbeInjuriesVo> getPbeInjuriesByXsemId(String xsemId) {
        String sql = "SELECT " +
                "A.pbe_id pbeId," +
                "st_area ( st_intersection (  A.geom, b.geom ) ) AS area," +
                "A.dz_intensity dzIntensity," +
                "A.steel_area steelArea," +
                "A.c_area cArea," +
                "A.b_area bArea," +
                "A.w_area wArea," +
                "A.o_area oArea," +
                "A.destroy destroy," +
                "A.covered_area coveredArea,"+
                "b.earthquake_level earthquakeLevel," +
                "b.fsi_id fsiId " +
                " FROM " +
                " data_house_pbe_int_str_area " +
                " A LEFT JOIN ( SELECT xsem_id, intensity, geom, earthquake_level, fsi_id FROM xzch_fast_seismic_influence WHERE xsem_id = '"+xsemId+"' AND del_flag = '0' ) b ON A.xsem_id = b.xsem_id " +
                " WHERE" +
                " A.dz_intensity = b.intensity " +
                "  AND A.xsem_id = '"+xsemId+"'" +
                " AND st_intersects (  A.geom, b.geom )";

        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DataPbeInjuriesVo.class));
    }

    public int batchupdate(List<DataPbeIntStrArea> pbeIntStrAreaList) {
        String sql = "insert into data_house_pbe_int_str_area(id,pbe_id,dz_intensity,steel_area,c_area," +
                "b_area,w_area,o_area,destroy,covered_area," +
                "province,city,county,town,xsem_id,geom,province_code,city_code,county_code) " +
                " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,ST_GeometryFromText(?,4490),?,?,?)";
        System.out.println("开始批量插入");
        int[] updateCountArray = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, pbeIntStrAreaList.get(i).getId());
                ps.setString(2, pbeIntStrAreaList.get(i).getPbeId());
                ps.setString(3, pbeIntStrAreaList.get(i).getDzIntensity());
                ps.setFloat(4, pbeIntStrAreaList.get(i).getSteelArea().floatValue());
                ps.setFloat(5, pbeIntStrAreaList.get(i).getCArea().floatValue());
                ps.setFloat(6, pbeIntStrAreaList.get(i).getBArea().floatValue());
                ps.setFloat(7, pbeIntStrAreaList.get(i).getWArea().floatValue());
                ps.setFloat(8, pbeIntStrAreaList.get(i).getOArea().floatValue());
                ps.setString(9, pbeIntStrAreaList.get(i).getDestroy());
                ps.setFloat(10, pbeIntStrAreaList.get(i).getCoveredArea().floatValue());
                ps.setString(11, pbeIntStrAreaList.get(i).getProvince());
                ps.setString(12, pbeIntStrAreaList.get(i).getCity());
                ps.setString(13, pbeIntStrAreaList.get(i).getCounty());
                ps.setString(14, pbeIntStrAreaList.get(i).getTown());
                ps.setString(15, pbeIntStrAreaList.get(i).getXsemId());
                ps.setString(16, pbeIntStrAreaList.get(i).getGeom());
                ps.setString(17, pbeIntStrAreaList.get(i).getProvinceCode());
                ps.setString(18, pbeIntStrAreaList.get(i).getCityCode());
                ps.setString(19, pbeIntStrAreaList.get(i).getCountyCode());;
            }
            @Override
            public int getBatchSize() {
                return pbeIntStrAreaList.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }

    public void saveHouseDestroyEvaluateModel(HouseDestroyEvaluateModel model) {
        String sql="insert into xczh_fast_house_evalate_model(xfhem_id," +
                "building_version," +
                "influence_version," +
                "fv_id," +
                "evaluate_user," +
                "evaluate_time," +
                "xsem_id) " +
                "values(:xfhemId, :buildingVersion, :influenceVersion, :fvId, :evaluateUser, :evaluateTime, :xsemId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(model);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source);
    }

    public void batchSave(List<HouseDestroyEvaluateResult> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO xczh_fast_house_evalate_result (xfher_id," +
                "xsem_id," +
                "fsi_id," +
                "xfhem_id," +
                "geom," +
                "dz_intensity," +
                "basic," +
                "basic_area," +
                "slight," +
                "slight_area," +
                "mid," +
                "mid_area," +
                "serious," +
                "serious_area," +
                "destroy," +
                "destroy_area,total_area,influence_version,intensity)" +
                "values (:xfherId,:xsemId,:fsiId,:xfhemId,ST_GeometryFromText(:geom,4490),:dzIntensity,:basic," +
                ":basicArea,:slight,:slightArea,:mid,:midArea,:serious,:seriousArea,:destroy,:destroyArea,:totalArea,:influenceVersion,:intensity)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public void deleteDataPbeIntStrAreaByXsemId(String xsemId) {
        jdbcTemplate.update("delete from  data_house_pbe_int_str_area  where xsem_id='"+xsemId+"'");
    }

    public void deleteHouseEvaluateModel(String xsemId) {
        jdbcTemplate.update("delete from  xczh_fast_house_evalate_model  where xsem_id='"+xsemId+"'" );
    }

    public void deleteHouseEvaluateResult(String xsemId) {
        jdbcTemplate.update("delete from  xczh_fast_house_evalate_result  where xsem_id='"+xsemId+"'");
    }

    public List<HouseDestroyEvaluateResult> getHouseDestoryResult(String xsemId,String dzIntensity) {
        StringBuilder sql = new StringBuilder("select *,st_astext(geom) as geom from xczh_fast_house_evalate_result ");
        StringBuilder whereSql = new StringBuilder("where xsem_id='"+xsemId+"' ");
        String orderSql = "order by dz_intensity asc";
        if(PlatformObjectUtils.isNotEmpty(dzIntensity)){
            whereSql.append("and dz_intensity='"+dzIntensity+"' ");

        }
        sql.append(whereSql).append(orderSql);
        List<HouseDestroyEvaluateResult> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouseDestroyEvaluateResult.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query:new ArrayList<>();
    }

    public HouseDestroyEvaluateResult getHouseDestoryAreaSum(String xsemId) {
        String sql = "select fsi_id,sum(basic_area) basicArea,sum(slight_area) slightArea,sum(mid_area) midArea,sum(serious_area) seriousArea,\n" +
                "sum(destroy_area) destroyArea from xczh_fast_house_evalate_result where xsem_id='"+xsemId+"' GROUP BY fsi_id";
        HouseDestroyEvaluateResult houseDestroyEvaluateResult = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseDestroyEvaluateResult.class));
        return houseDestroyEvaluateResult;
    }

    public void deleteDataHousePbeIntStrArea() {
        jdbcTemplate.update("delete from data_house_pbe_int_str_area");
    }

    public HouseDestroyEvaluateModel getModelData(String xsemId) {
        List<HouseDestroyEvaluateModel> query = jdbcTemplate.query("select * from xczh_fast_house_evalate_model where xsem_id='" + xsemId + "'", new BeanPropertyRowMapper<>(HouseDestroyEvaluateModel.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):null;
    }


}
